import os
import pandas as pd
import requests
import json
import pymysql
from utils import df_into_db


def get_symbols_info_from_api():
    url = "https://api.binance.com/api/v3/exchangeInfo"
    req = requests.get(url)
    all_data = json.loads(req.content)
    symbols = all_data['symbols']
    print(f"所有币种数量:{len(symbols)}")
    symbols_info = {}
    [symbols_info.update({x['symbol']:(x['baseAsset'], x['quoteAsset'])}) for x in symbols if x['status'] == 'TRADING']
    return symbols_info


def main():
    frequency = "1d"
    path = f"G:/binance_k_line/v1/{frequency}"
    files = os.listdir(path)
    symbols = [file.split("_")[1] for file in files]
    symbols_info = get_symbols_info_from_api()
    one_hour_grp = 10
    five_minute_grp = 100
    one_hour_hash_list = []
    five_minute_hash_list = []
    base_asset_list = []
    quote_asset_list = []
    for symbol in symbols:
        one_hour_hash = hash(symbol) % one_hour_grp + 1
        five_minute_hash = hash(symbol) % five_minute_grp + 1
        # print(symbol, one_hour_hash, five_minute_hash)
        one_hour_hash_list.append(one_hour_hash)
        five_minute_hash_list.append(five_minute_hash)
        base_asset, quote_asset = symbols_info.get(symbol)
        base_asset_list.append(base_asset)
        quote_asset_list.append(quote_asset)
    df = pd.DataFrame({"symbol":symbols,
                       "one_hour_hash": one_hour_hash_list,
                       "five_minute_hash": five_minute_hash_list,
                       "base_asset":base_asset_list,
                       "quote_asset":quote_asset_list}
    )
    df_into_db(df, db_name="all_history_ohlcvm_coinmarketcap", table_name="binance_symbol_info")


def crate_table():
    db_config = {
        "charset": "utf8",
        "host": "192.168.0.114",
        "port": 3307,
        "user": "admin",
        "passwd": "123456"
    }
    conn = pymysql.connect(db="binance", **db_config)
    cursor = conn.cursor()
    for frequency in ["1d", "1h", "5m"]:
        if frequency == "1d":
            suffix_list = ["1d"]
        elif frequency == "1h":
            suffix_list = [f"1h_{i}" for i in range(1, 11)]
        else:
            suffix_list = [f"5m_{i}" for i in range(1, 101)]
        for suffix in suffix_list:
            sql = f"""
                CREATE TABLE `binance_k_line_{suffix}` (
              `id` int NOT NULL AUTO_INCREMENT,
              `datetime` datetime NOT NULL COMMENT '时间(转为UTC)',
              `symbol` char(30) NOT NULL COMMENT '交易对',
              `timestamp` bigint NOT NULL COMMENT '时间戳',
              `frequency` char(10) NOT NULL COMMENT '频率, 1h或1d',
              `datasource` char(10) NOT NULL DEFAULT 'binance' COMMENT 'binance',
              `type` char(10) NOT NULL COMMENT '类型:现货或指数',
              `open` decimal(34,12) DEFAULT NULL COMMENT '开盘价格',
              `high` decimal(34,12) DEFAULT NULL COMMENT '最高价',
              `low` decimal(34,12) DEFAULT NULL COMMENT '最低价',
              `close` decimal(34,12) DEFAULT NULL COMMENT '收盘价格',
              `vol` decimal(34,12) DEFAULT NULL COMMENT '成交量',
              `amount` decimal(34,12) DEFAULT NULL COMMENT '成交额',
              `number_of_trades` int DEFAULT NULL COMMENT '成交笔数',
              `active_vol` decimal(34,12) DEFAULT NULL COMMENT '主动买入成交量',
              `active_amount` decimal(34,12) DEFAULT NULL COMMENT '主动买入成交额',
              `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '数据入库时间',
              `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据最后更新时间',
              PRIMARY KEY (`id`),
              UNIQUE KEY `unique_datetime` (`datetime`,`frequency`,`datasource`,`symbol`,`type`) USING BTREE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='binance_k_line_{suffix}';
            """
            cursor.execute(sql)
            conn.commit()
            print(f"create table binance_k_line_{suffix} successfully")
    cursor.close()
    conn.close()


if __name__ == "__main__":
    crate_table()


